﻿Imports Microsoft.Data.Sqlite
Imports System.IO



Public Class Form1


    Private connectionString As String = "Data Source=encrypted.db;Password=123;"
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        CreateEncryptedDatabase()
    End Sub
    Public Sub CreateEncryptedDatabase()
        Try
            Using connection As New SqliteConnection(connectionString)
                connection.Open()

                ' 设置加密密钥（PRAGMA key）
                Using cmd As New SqliteCommand("PRAGMA key='123';", connection)
                    cmd.ExecuteNonQuery()
                End Using

                ' 创建表
                Using cmd As New SqliteCommand("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);", connection)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            MessageBox.Show("加密数据库创建成功！")
        Catch ex As Exception
            MessageBox.Show("错误: " & ex.Message)
        End Try
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then MsgBox("内容不能为空！") : Exit Sub
        If TextBox2.Text <> TextBox3.Text Then MsgBox("两次输入的密码不一致") : Exit Sub
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                ' 检查账号是否已存在 
                Dim checkSql As String = "SELECT COUNT(*) FROM userlist WHERE 账号 = @account"
                Using checkCmd As New SqliteCommand(checkSql, connection)
                    checkCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim())
                    Dim count As Integer = Convert.ToInt32(checkCmd.ExecuteScalar())
                    If count > 0 Then
                        MsgBox("已有相同账号，请修改", vbOKOnly, "提示")
                        Exit Sub
                    End If
                End Using
                ' 插入新用户 
                Dim insertSql As String = "INSERT INTO userlist (账号, 密码) VALUES (@account, @password)"
                Using insertCmd As New SqliteCommand(insertSql, connection)
                    insertCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim())
                    insertCmd.Parameters.AddWithValue("@password", EncryptString(TextBox2.Text))
                    insertCmd.ExecuteNonQuery()
                    MsgBox("注册成功！", vbOKOnly, "提示")
                End Using
                FetchDataAndPopulateGrid()
            Catch ex As Exception
                MessageBox.Show($"操作失败: {ex.Message}")
            End Try
        End Using
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        FetchDataAndPopulateGrid()
    End Sub

    Public Sub FetchDataAndPopulateGrid()
        SQLitePCL.Batteries.Init()  ' 初始化加密支持‌:ml-citation{ref="3,6" data="citationList"}

        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT 账号, 密码, 是否注销 FROM userlist WHERE 是否注销='false'"
                Using cmd As New SqliteCommand(strsql, connection)
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        Dim dataTable As New DataTable()
                        dataTable.Load(reader)
                        If dataTable.Rows.Count > 0 Then
                            DataGridView1.DataSource = dataTable
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then MsgBox("内容不能为空！") : Exit Sub
        If TextBox2.Text <> TextBox3.Text Then MsgBox("两次输入的密码不一致") : Exit Sub
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                ' 检查账号是否已存在 
                Dim checkSql As String = "SELECT 密码 FROM userlist WHERE 账号 = @account"
                Using checkCmd As New SqliteCommand(checkSql, connection)
                    checkCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim())
                    Dim reader As SqliteDataReader = checkCmd.ExecuteReader()
                    If reader.HasRows Then
                        ' 检查是否和原密码一致
                        reader.Read()
                        Dim oldpassword As String = DecryptString(reader.GetString(reader.GetOrdinal("密码")))
                        If TextBox2.Text.Trim() = oldpassword Then
                            MsgBox("新密码和原密码一致，请检查", vbOKOnly, "提示")
                            Exit Sub
                        End If
                        reader.Close()
                        '修改密码
                        Dim updateSql As String = "UPDATE userlist SET 密码 = @password WHERE 账号 = @account"
                        Dim updateCmd As New SqliteCommand(updateSql, connection)
                        updateCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim())
                        updateCmd.Parameters.AddWithValue("@password", EncryptString(TextBox2.Text))
                        updateCmd.ExecuteNonQuery()
                        MsgBox("修改成功！", vbOKOnly, "提示")
                    Else
                        MsgBox("不存在该账号，请修改", vbOKOnly, "提示")
                        Exit Sub
                    End If
                End Using

                FetchDataAndPopulateGrid()
            Catch ex As Exception
                MessageBox.Show($"操作失败: {ex.Message}")
            End Try
        End Using
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then MsgBox("内容不能为空！") : Exit Sub
        If TextBox2.Text <> TextBox3.Text Then MsgBox("两次输入的密码不一致") : Exit Sub
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                ' 检查账号是否已存在 
                Dim checkSql = "SELECT 密码 FROM userlist WHERE 账号 = @account"
                Using checkCmd As New SqliteCommand(checkSql, connection)
                    checkCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim)
                    Dim reader = checkCmd.ExecuteReader
                    If reader.HasRows Then
                        ' 检查是否和原密码一致
                        reader.Read()
                        Dim oldpassword = DecryptString(reader.GetString(reader.GetOrdinal("密码")))
                        If TextBox2.Text.Trim <> oldpassword Then
                            MsgBox("密码不正确，请检查", vbOKOnly, "提示")
                            Exit Sub
                        End If
                        reader.Close()
                        '临时删除账号
                        'Dim updateSql As String = "update userlist SET 是否注销 = @isconcel WHERE 账号 = @account"
                        'Dim updateCmd As New sqliteCommand(updateSql, connection)
                        'updateCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim())
                        'updateCmd.Parameters.AddWithValue("@isconcel", "true")
                        'updateCmd.ExecuteNonQuery()
                        'MsgBox("删除成功！", vbOKOnly, "提示")

                        '永久删除账号
                        Dim updateSql = "delete from userlist WHERE 账号 = @account"
                        Dim updateCmd As New SqliteCommand(updateSql, connection)
                        updateCmd.Parameters.AddWithValue("@account", TextBox1.Text.Trim)
                        updateCmd.ExecuteNonQuery()
                        MsgBox("删除成功！", vbOKOnly, "提示")
                    Else
                        MsgBox("不存在该账号，请修改", vbOKOnly, "提示")
                        Exit Sub
                    End If
                End Using

                FetchDataAndPopulateGrid()
            Catch ex As Exception
                MessageBox.Show($"操作失败: {ex.Message}")
            End Try
        End Using
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    'TextBox4.Text = EncryptString("Data Source=|DataDirectory|\db\MQC2020.db;Password=1;Version=3;")


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        TextBox4.Text = EncryptString("db\MQC2020.db;Password=2020")


        'MsgBox(_connString)
        FetchDataAndPopulateGrid()




    End Sub


End Class